from pymysql import connect


class Data(object):
    def __init__(self):
        self.__conn = connect(host='localhost', port=3306, database='python_db', user='mysql', password='mysql', charset='utf8')
        self.__cur = self.__conn.crusor()

    def __del__(self):
        self.__cur.close()
        self.__conn.close()

    def run(self):
        while True:
            print("1.查询所有商品信息")
            print("2.查询所有商品的种类信息")
            print("3.查询所有商品的品牌信息")
            print("4.添加商品种类")
            print("5.根据id查询商品信息")
            print("6.根据id查询商品信息安全方式")

            select_id = input('请选择功能ID:')

            if select_id == '1':
                self.__fetch_all_info()

            elif select_id == '2':
                self.__fetch_cate()

            elif select_id == '3':
                self.__fetch_brand()

            elif select_id == '4':
                cate_name = input('请输入一个商品种类:')
                self.__add_info(cate_name)

            elif select_id == '5':

                good_id = input('请输入要查询的商品ID')
                self.__find_info(good_id)


            elif select_id == '6':
                good_id = input('请输入要查询的商品ID')
                self.__find_info_safe(good_id)

            else:
                print("请输入正确的ID")

    def __fetch_all_info(self):
        sql_str = '''select * from goods;'''
        self.__cur.execute(sql_str)
        result_all = self.__cur.fetchall()
        self.__show_query_result(result_all)

    def __show_query_result(self, result_all):
        for i in result_all:
            print(i)

    def __fetch_cate(self):
        sql_str = '''select * from goods_cates;'''
        self.__cur.execute(sql_str)
        result_all = self.__cur.fetchall()
        self.__show_query_result(result_all)

    def __fetch_brand(self):
        sql_str = '''select * from goods_brands;'''
        self.__cur.execute(sql_str)
        result_all = self.__cur.fetchall()
        self.__show_query_result(result_all)

    def __add_info(self, cate_name):
        sql_str = '''insert into goods_cates(name) values(%s);'''
        self.__cur.execute(sql_str)
        self.__cur.commit()

    def __find_info(self, good_id):
        sql_str = '''select * from goods where id = %s ;'''
        self.__cur.execute(sql_str)
        result_all = self.__cur.fetchall()
        self.__show_query_result(result_all)

    def __find_info_safe(self, good_id):
        sql_str = '''select * from goods where id = %s ;'''
        self.__cur.execute(sql_str,(good_id,))
        result_all = self.__cur.fetchall()
        self.__show_query_result(result_all)


def main():
    while True:
        ku = Data()
        ku.run()


if __name__ == '__main__':
    main()